package com.ssyt.ec.assit.service;

import java.util.ArrayList;
import java.util.List;

import com.ssyt.ec.assit.entity.AssistEntity;
import com.ssyt.ec.assit.entity.AssistMinuteEntity;
import com.ssyt.ec.assit.entity.AssistUpdateSearchEntity;
import com.ssyt.ec.assit.entity.MenuSearchEntity;
import com.ssyt.ec.common.PageModel;
import com.ssyt.ec.common.util.DBUtil;

public class AssistService {

	/**
	 * 辅助模块-站内信息查询业务
	 * 
	 * @param userId
	 * @param page
	 * @param rows
	 * @param titleSearch
	 * @param sendTypeSearch
	 * @param senderSearch
	 * @param infoStateSearchInt
	 * @param expireSearchInt
	 * @return
	 */
	public PageModel<AssistEntity> listAssistStationQuery(int userId, int page,
			int rows, String titleSearch, String sendTypeSearch,
			String senderSearch, int infoStateSearchInt, int expireSearchInt) {
		List<Object> param = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder(
				"SELECT a.id,a.title,a.state,a.content, ");
		sql.append("su.name AS sendername,sr.name AS receivername,a.send_time,a.expire_time ");
		sql.append("FROM assist_message a,sys_user su,sys_role sr ");
		sql.append("WHERE a.sender_id=su.id AND su.role_id=sr.id ");
		if ("1".equals(sendTypeSearch)) {
			sql.append("AND a.sender_id=? ");
			param.add(userId);
		} else {
			sql.append("AND a.sender_id NOT in(?) ");
			param.add(userId);
		}
		if (senderSearch != null) {
			sql.append("AND sr.id=? ");
			param.add(senderSearch);
		}
		if (infoStateSearchInt != 0) {
			sql.append("AND a.state=? ");
			param.add(infoStateSearchInt);
		}
		if (expireSearchInt != 0) {
			if (expireSearchInt == 1) {
				sql.append("AND a.expire_time<NOW() ");
			} else {
				sql.append("AND a.expire_time>NOW() ");
			}
		}
		if (titleSearch != null) {
			sql.append("AND a.title LIKE ? ");
			param.add("%" + titleSearch + "%");
		}
		sql.append("ORDER BY a.id DESC LIMIT ?,?");
		param.add((page - 1) * rows);
		param.add(rows);
		return DBUtil.executeQueryByPage(AssistEntity.class, sql.toString(),
				param);
	}

	/**
	 * 状态操作业务
	 * 
	 * @param rowId
	 * @param stateIndex
	 * @return
	 */
	public int updateState(int rowId, int stateIndex) {
		String sql = "UPDATE assist_message SET state=? WHERE assist_message.id=?;";
		return DBUtil.executeUpdate(sql, stateIndex, rowId);

	}

	/**
	 * 站内信息详细
	 * 
	 * @param id
	 * @return
	 */
	public AssistMinuteEntity listAssistStationMinute(int id) {
		StringBuilder sql = new StringBuilder(
				"SELECT a.id,a.title,a.state,a.content,su.name AS sendername, ");
		sql.append("sr.name AS receivername,a.send_time,su.name AS acceptname,a.expire_time,sc.name AS classname ");
		sql.append("FROM assist_message a,sys_user su,sys_role sr,sys_class sc ");
		sql.append("WHERE a.receiver_class_ids=sc.id AND a.sender_id=su.id AND su.role_id=sr.id AND a.id=?");
		return DBUtil.getUniqueResult(AssistMinuteEntity.class, sql.toString(),
				id);
	}

	/**
	 * 条件查询的发送角色生成下拉业务
	 * 
	 * @return
	 */
	public List<MenuSearchEntity> senderCombobox() {
		return DBUtil.executeQuery(MenuSearchEntity.class,
				"SELECT id,name FROM sys_role;");
	}

	/**
	 * 指定人员的业务
	 * 
	 * @return
	 */
	public List<MenuSearchEntity> searchNameAdd() {
		String sql = "SELECT id,name FROM sys_user;";
		return DBUtil.executeQuery(MenuSearchEntity.class, sql);
	}

	/**
	 * 指定班级
	 * 
	 * @return
	 */
	public List<MenuSearchEntity> appointClassnameAdd() {
		String sql = "SELECT id,name FROM sys_class;";
		return DBUtil.executeQuery(MenuSearchEntity.class, sql);
	}

	/**
	 * 站内信新增
	 * 
	 * @param titleName
	 * @param content
	 * @param receiver_user_ids
	 * @param receiver_class_ids
	 * @param expire_time
	 * @param state
	 * @param userId
	 * @return
	 */
	public int assistInfoAdd(String titleName, String content,
			int receiver_user_ids, int receiver_class_ids, String expire_time,
			int state, int userId) {
		List<Object> paramList = new ArrayList<Object>();
		String sql = "INSERT INTO assist_message VALUES (NULL, ?, ?, NOW(), ?, ?, ?, ?, ?)";
		paramList.add(titleName);
		paramList.add(content);
		paramList.add(userId);
		paramList.add(receiver_user_ids);
		paramList.add(receiver_class_ids);
		paramList.add(expire_time);
		paramList.add(state);
		return DBUtil.executeUpdate(sql, paramList);
	}

	/**
	 * 站内信息修改查询
	 * 
	 * @param rowId
	 * @return
	 */
	public AssistUpdateSearchEntity listUpdateMinute(int rowId) {
		String sql = "SELECT am.id,am.title,am.content,am.expire_time,am.state,"
				+ "su.name AS acceptname,sc.name AS classname "
				+ "FROM assist_message am,sys_user su,sys_class sc "
				+ "WHERE am.sender_id=su.id AND am.receiver_class_ids=sc.id AND am.id=?;";
		return DBUtil.getUniqueResult(AssistUpdateSearchEntity.class, sql,
				rowId);
	}

	/**
	 * 站内信息修改
	 * 
	 * @param id
	 * @param state
	 * @param expire_time
	 * @param classnameInt
	 * @param acceptnameInt
	 * @param content
	 * @param title
	 * @return
	 */
	public int assistInfoUpdate(int id, String title, String content,
			String acceptname, String classname, String expire_time, int state) {
		String sql = "UPDATE assist_message am,sys_user su,sys_class sc SET am.title=?,am.content=?,su.name=?,sc.name=?,am.expire_time=?,am.state=? "
				+ "WHERE am.sender_id=su.id AND am.receiver_class_ids=sc.id AND am.id=?;";
		return DBUtil.executeUpdate(sql, title, content, acceptname, classname,
				expire_time, state, id);
	}
}
